﻿Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Diagnostics
Imports System.Reflection

Public MustInherit Class ExcelFileWriter(Of T)
    Private _excelApplication As Microsoft.Office.Interop.Excel.Application = Nothing
    Private _workBooks As Microsoft.Office.Interop.Excel.Workbooks = Nothing
    Private _workBook As Microsoft.Office.Interop.Excel._Workbook = Nothing
    Private _value As Object = Missing.Value
    Private _excelSheets As Microsoft.Office.Interop.Excel.Sheets = Nothing
    Private _excelSheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
    Private _excelRange As Microsoft.Office.Interop.Excel.Range = Nothing
    Private _excelFont As Microsoft.Office.Interop.Excel.Font = Nothing
    Protected colNumber, rowNumber As Integer
    Public MustOverride Property Headers() As Object()
    Public MustOverride Sub FillRowData(ByVal list As List(Of T))
    Public MustOverride ReadOnly Property ExcelData() As Object(,)

    Public ReadOnly Property ColumnCount() As Integer
        Get
            Return colNumber
        End Get
    End Property

    Public ReadOnly Property RowCount() As Integer
        Get
            Return rowNumber
        End Get
    End Property

    Protected Overridable ReadOnly Property BoldHeaders() As Boolean
        Get
            Return True
        End Get
    End Property

    Public Sub WriteDateToExcel(ByVal fileName As String, ByVal list As List(Of T), ByVal startColumnOfHeader As String, ByVal endColumnOfHeader As String, ByVal startColumnOfData As String)
        Me.ActivateExcel()
        Me.FillRowData(list)
        Me.FillExcelWithData(startColumnOfData)
        Me.FillHeaderColumn(Headers, startColumnOfHeader, endColumnOfHeader)
        Me.SaveExcel(fileName)
    End Sub

    Protected Overridable Sub ActivateExcel()
        _excelApplication = New Microsoft.Office.Interop.Excel.Application()
        _workBooks = CType(_excelApplication.Workbooks, Microsoft.Office.Interop.Excel.Workbooks)
        _workBook = CType(_workBooks.Add(_value), Microsoft.Office.Interop.Excel._Workbook)
        _excelSheets = CType(_workBook.Worksheets, Microsoft.Office.Interop.Excel.Sheets)
        _excelSheet = CType(_excelSheets.Item(1), Microsoft.Office.Interop.Excel._Worksheet)
    End Sub

    Protected Sub FillHeaderColumn(ByVal headers() As Object, ByVal startColumn As String, ByVal endColumn As String)
        _excelRange = _excelSheet.Range(startColumn, endColumn)
        _excelRange.Value(_value) = headers
        If BoldHeaders = True Then

            Me.BoldRow(startColumn, endColumn)
        End If
        _excelRange.EntireColumn.AutoFit()
    End Sub

    Private Sub FillExcelWithData(ByVal startColumn As String)
        _excelRange = _excelSheet.Range(startColumn, _value)
        _excelRange = _excelRange.Resize(RowCount + 1, ColumnCount)
        _excelRange.Value(Missing.Value) = ExcelData
        _excelRange.EntireColumn.AutoFit()
    End Sub

    Protected Overridable Sub SaveExcel(ByVal fileName As String)
        _workBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, _value, _value, _value, _value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, _value, _value, _value, _value, Nothing)
        _workBook.Close(False, _value, _value)
        _excelApplication.Quit()
    End Sub

    Private Sub BoldRow(ByVal row1 As String, ByVal row2 As String)
        _excelRange = _excelSheet.Range(row1, row2)

        _excelFont = _excelRange.Font
        _excelFont.Bold = True
    End Sub

    Protected Overridable Sub Merge(ByVal col1 As Integer, ByVal row1 As Integer, ByVal col2 As Integer, ByVal row2 As Integer)
        _excelRange = _excelSheet.Range(_excelSheet.Cells(col1, row1), _excelSheet.Cells(col1, row2))
        _excelRange.Merge(True)
    End Sub
End Class
